package saleMarket.dao;

import saleMarket.domain.Customer;
import util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.TreeSet;

/**
 * @author 徐元坤
 * @version 1.0  12-07-2020
 */

public class CustomerDao {
    /**
     *单例模式
     *该类负责创建自己的对象，同时确保只有单个对象被创建。
     * 这个类提供了一种访问其唯一的对象的方式，可以直接访问，不需要实例化该类的对象。
     */
    private static CustomerDao customerDao = new CustomerDao();
    private CustomerDao(){}

    public static CustomerDao getInstance(){return customerDao;}
    /**
     * @param conn      连接
     * @param conn 来自Service的Connection对象
     * @return Customer对象
     * @throws SQLException
     */
    //查询单个顾客方法
    public Customer find(Integer id, Connection conn) throws SQLException {
        Customer customer = null;
        //编辑预编译语句
        PreparedStatement find_pstmt = conn.prepareStatement("SELECT * FROM customer WHERE id = ?");
        //设置预编译参数
        find_pstmt.setInt(1,id);
        //执行查询并获取查询结果
        ResultSet find_resultSet = find_pstmt.executeQuery();
        //赋值
        if (find_resultSet.next()){
            String no = find_resultSet.getString("no");
            String name = find_resultSet.getString("name");
            String sex = find_resultSet.getString("sex");
            String signature = find_resultSet.getString("signature");
            Integer phoneNumber = find_resultSet.getInt("phoneNumber");
            String address = find_resultSet.getString("address");
            //创建相应的顾客对象
            customer = new Customer(id,no,name,sex,signature,phoneNumber,address);
        }
        //释放资源
        JdbcHelper.close(find_resultSet,find_pstmt);
        //返回查询结果
        return customer;
    }
    /**
     * @param conn       连接
     * @param conn 来自Service的Connection对象
     * @return collection<Customer></>
     * @throws SQLException
     */
    //查询所有顾客方法
    public Collection<Customer> findAll(Connection conn) throws SQLException {
        Collection<Customer> customers = new ArrayList<Customer>();
        //编辑预编译语句
        PreparedStatement findAll_pstmt = conn.prepareStatement("SELECT * FROM customer");
        //执行查询并获取查询结果
        ResultSet findAll_resultSet = findAll_pstmt.executeQuery();
        //赋值
        while (findAll_resultSet.next()){
            Integer id = findAll_resultSet.getInt("id");
            String no = findAll_resultSet.getString("no");
            String name = findAll_resultSet.getString("name");
            String sex = findAll_resultSet.getString("sex");
            String signature = findAll_resultSet.getString("signature");
            Integer phoneNumber = findAll_resultSet.getInt("phoneNumber");
            String address = findAll_resultSet.getString("address");
            //创建相应的顾客对象
            Customer customer = new Customer(id,no,name,sex,signature,phoneNumber,address);
            //将查询出的单个顾客添加进集合中
            customers.add(customer);
        }
        //释放资源
        JdbcHelper.close(findAll_resultSet,findAll_pstmt);
        //返回查询结果
        return customers;
    }
    /**
     * @param customer 要增加到数据库表中的Customer对象
     * @param conn 来自Service的Connection对象
     * @return 成功增加true，失败：false
     * @throws SQLException
     */
    //增加单个顾客方法
    public boolean add(Customer customer,Connection conn) throws SQLException{
        //编辑预编译语句
        PreparedStatement add_pstmt = conn.prepareStatement("" +
                "INSERT INTO customer(id,no,name,sex,signature,phoneNumber,address)VALUES (?,?,?,?,?,?,?)");
        //设置预编译参数
        add_pstmt.setInt(1,customer.getId());
        add_pstmt.setString(2,customer.getNo());
        add_pstmt.setString(3,customer.getName());
        add_pstmt.setString(4,customer.getSex());
        add_pstmt.setString(5,customer.getSignature());
        add_pstmt.setInt(6,customer.getPhoneNumber());
        add_pstmt.setString(7,customer.getAddress());
        //获取执行结果
        int affectedRow = add_pstmt.executeUpdate();
        //释放资源
        JdbcHelper.close(add_pstmt);
        //返回执行结果
        return affectedRow>0;
    }
    /**
     * @param conn  customer 要更新到数据库表中的Customer对象
     * @param conn 来自Service的Connection对象
     * @return update成功返回true，反之，false
     * @throws SQLException
     */
    //更新顾客信息方法
    public boolean update(Customer customer,Connection conn) throws SQLException{
        //编辑预编译语句
        PreparedStatement update_pstmt = conn.prepareStatement("UPDATE customer SET" +
                " no=?,name=?,sex=?,signature=?,phoneNumber=?,address=? WHERE id=?");
        //设置预编译参数
        update_pstmt.setInt(7,customer.getId());
        update_pstmt.setString(1,customer.getNo());
        update_pstmt.setString(2,customer.getName());
        update_pstmt.setString(3,customer.getSex());
        update_pstmt.setString(4,customer.getSignature());
        update_pstmt.setInt(5,customer.getPhoneNumber());
        update_pstmt.setString(6,customer.getAddress());
        //获取执行结果
        int affectedRow = update_pstmt.executeUpdate();
        //释放资源
        JdbcHelper.close(update_pstmt);
        //返回执行结果
        return affectedRow>0;
    }
    /**
     * @param conn  customer 要删除数据库表中的Customer对象
     * @param conn 来自Service的Connection对象
     * @return 删除成功返回true，反之，false
     * @throws SQLException
     */
    //删除顾客方法
    public boolean delete(Integer id,Connection conn)throws SQLException{
        //设置预编译语句
        PreparedStatement delete_pstmt = conn.prepareStatement("DELETE FROM customer WHERE id=?");
        //设置预编译参数
        delete_pstmt.setInt(1,id);
        //获取执行结果
        int affectedRow = delete_pstmt.executeUpdate();
        //释放资源
        JdbcHelper.close(delete_pstmt);
        //返回执行结果
        return affectedRow>0;
    }
}
